Processing Top N and Bottom N Queries

In certain application areas, such as those related to decision support or multimedia data, users wish to ask so-called top N and bottom N queries; these are queries that request a certain number of answers (N) having the highest or lowest values for some attribute, expression, or function. For example, rather than nding all publications on a certain topic, a researcher may want to retrieve the ten most heavily referenced papers on the topic at hand. A politician planning his or her next campaign might be interested in discovering the average salary of the wealthiest ten percent of the voters in a given district. Parents of a young child might want to nd ve mystery books that least well match the terms \crime" and \murder." These examples illustrate a variety of situations in which top N and bottom N queries are meaningful. In addition, they demonstrate the fact that such queries can involve standard relational data as well as text or other multimedia data. To date, the SQL standard does not include statements that allow users to pose such top N and bottom N queries. There have, however, been several proposals in the literature (e.g., [KS95, CG96, CK97]), and database system vendors are beginning to extend their SQL dialects and query interfaces in order to support such queries. Given the obvious need and this growing interest, this paper addresses the question of how top N and bottom N queries can be processed e ciently; moreover, we address the question of how such support can be provided as a natural extension of existing relational query processing architectures. In a nutshell, our goal is to evaluate such queries with as little wasted work as possible. That is, if a query asks for the 10 most popular publications, we want to avoid work to process, say, the 11th, 12th, or 13th most popular publications. We will begin by presenting a series of situations in which a traditional DBMS, i.e., one without integrated support for top N and bottom N queries, would end up wasting work. We then show how such a traditional DBMS could be extended { with relatively little e ort, in fact { in order to avoid such wasted work and thereby achieve orders-of-magnitude improvements in many cases. Our goal here is to drive home the point that database systems must be extended in order to process top N and bottom N queries e ciently and to brie y touch upon each of the required extensions; a detailed description of our approach, as well as a performance evaluation, can be found in [CK97]. We will focus here on SQL and relational databases, using relational queries as examples and citing some measurements from a relational DBMS platform to illustrate the important performance gains that can be achieved; we note, however, that most of the e ects and techniques discussed in this paper are applicable to any kind of database system.